﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Baimasoft.Manage.SqlServerDAL;
using System.Data;
using HsTec.Mobile.Green.Util;
using System.Text;

public partial class ReportStorage_ExportProvinceReport : System.Web.UI.Page
{
    Param MyP = new Param();
    Dictionary<string, string> dic = new Dictionary<string, string>();
    protected string sAreaId = String.Empty;
    protected void Page_Load(object sender, EventArgs e)
    {
        if ((string)Session["Baimasoft_Pers_ID"] == null)
        {
            Response.Redirect("../Logon.aspx");
        }
        sAreaId = (string)Session["Baimasoft_Report"];
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        string sStartYears = sltStartYear.Value.Trim() + sltStartMonth.Value;
        string sEndYears = sltEndYear.Value.Trim() + sltEndMonth.Value;
        GetExportList(sStartYears, sEndYears, 1);
        hidState.Value = "1";
    }
    private void GetExportList(string sStartYears, string sEndYears, int iType)
    {
        String sTD = String.Empty;
        //1.获取需要的数据
        DataView mydv = new DataView();
        string mysql = " SELECT RTRIM(b.name) AS colname, CASE WHEN h.id IS NOT NULL " +
                           "       THEN 'PK' ELSE '' END AS primarykey, type_name(b.xusertype) " +
                           "       + CASE WHEN b.colstat & 1 = 1 THEN '[ID(' + CONVERT(varchar, ident_seed(a.name)) " +
                           "       + ',' + CONVERT(varchar, ident_incr(a.name)) + ')]' ELSE '' END AS type, b.length, " +
                           "       CASE b.isnullable WHEN 0 THEN 'N' ELSE 'Y' END AS isnull, ISNULL(e.text, '') " +
                           "       AS [default], ISNULL(c.[value], '') AS descript " +
                           " FROM (SELECT g.id, g.colid " +
                           "         FROM sysindexes f, sysindexkeys g " +
                           "         WHERE f.id = g.id AND f.indid = g.indid AND f.indid > 0 AND f.indid < 255 AND " +
                           "               (f.status & 2048) <> 0) h RIGHT OUTER JOIN " +
                           "       syscolumns b INNER JOIN " +
                           "       sysobjects a ON b.id = a.id LEFT OUTER JOIN " +
                           "       sys.extended_properties c ON b.id = c.major_id AND b.colid = c.minor_id LEFT OUTER JOIN " +
                           "       syscomments e ON b.cdefault = e.id ON h.id = b.id AND h.colid = b.colid " +
                           " WHERE (a.id = OBJECT_ID('tb_ReportAnalysis')) AND b.name !='P_ID'" +
                           " ORDER BY b.colid";
        DataView dvField = SQLHelper.ExecuteDataView(mysql);
        string sField = String.Empty;
        for (int i = 0; i < dvField.Count; i++)
        {
            if (dvField[i]["type"].ToString() != "varchar")
            {
                if (dvField[i]["colname"].ToString() != "RA_ID" &&
                    dvField[i]["colname"].ToString() != "Area_ID" &&
                    dvField[i]["colname"].ToString() != "Years" &&
                    dvField[i]["colname"].ToString() != "AddDate" &&
                    dvField[i]["colname"].ToString() != "OperatUser" &&
                    dvField[i]["colname"].ToString() != "OperatDate" &&
                    dvField[i]["colname"].ToString() != "Remark"
                    )
                {
                    sField += sField == "" ? "sum(" + dvField[i]["colname"] + ") as " + dvField[i]["colname"] : ",sum(" + dvField[i]["colname"] + ") as " + dvField[i]["colname"];
                }
            }
            else
            {
                sField += sField == "" ? "max(" + dvField[i]["colname"] + ") as " + dvField[i]["colname"] : ",max(" + dvField[i]["colname"] + ") as " + dvField[i]["colname"];
            }
        }
        mydv = SQLHelper.ExecuteDataView("select " + sField + " from tb_ReportAnalysis where years>='" + sStartYears + "' and years<='" + sEndYears + "' and Area_ID='41'");
        //2.填充数值
        if (mydv.Count == 1)
        {
            DataTable dt = mydv.Table;
            for (int i = 0, j = 1; i < dt.Columns.Count - 1; i++, j++)
            {
                TextBox txt = (TextBox)this.FindControl("TextBox" + j);
                txt.Text = mydv[0][i].ToString();
            }
        }

        for (int i = 1; i <= 20; i++)
        {
            TextBox txt = (TextBox)this.FindControl("TextBox" + i);
            txt.Text = "";
        }
        //3.按分配比例填充
        string sPowers = String.Empty;
        sPowers = SQLHelper.ExecuteString("select sum([Power]) from rt_EnergyConsumption where years>='" + sStartYears + "' and years<='" + sEndYears + "' and Area_ID='41'");
        sPowers = String.IsNullOrEmpty(sPowers) ? "0.00" : sPowers;
        DataView dvPro = SQLHelper.ExecuteDataView("select Subentry,Proportion from tb_EnergyProportion where Area_ID='-1' and Years='" + sStartYears + "' and Subentry!='Powers' and Subentry!='Gas' and Subentry!='DieselFuel'");
        Dictionary<string, string> dicList = new Dictionary<string, string>();
        dicList.Add("Powers", sPowers);
        TextBox powers = (TextBox)this.FindControl("TextBox1");
        powers.Text = sPowers;
        for (int i = 0; i < dvPro.Count; i++)
        {
            try
            {
                double value = double.Parse(dvPro[i]["Proportion"].ToString()) * double.Parse(dicList[dvPro[i]["Subentry"].ToString().Substring(0, dvPro[i]["Subentry"].ToString().LastIndexOf('_'))]) / 100;
                sTD = value.ToString("0.00");
                dicList.Add(dvPro[i]["Subentry"].ToString(), value.ToString("0.00"));
            }
            catch
            {
                sTD = "0.00";
            }
            int j = i + 2;
            TextBox txt = (TextBox)this.FindControl("TextBox" + j);
            txt.Text = sTD;
        }
        //3.分配汽油
        sPowers = String.Empty;
        sPowers = SQLHelper.ExecuteString("select sum([Gasoline]) from rt_EnergyConsumption where years>='" + sStartYears + "' and years<='" + sEndYears + "' and Area_ID='41'");
        sPowers = String.IsNullOrEmpty(sPowers) ? "0.00" : sPowers;
        dvPro = SQLHelper.ExecuteDataView("select Subentry,Proportion from tb_EnergyProportion where Area_ID='-1' and Years='" + sStartYears + "' and Subentry like 'Gas_%'");
        dicList.Clear();
        dicList.Add("Gas", sPowers);
        powers = (TextBox)this.FindControl("TextBox15");
        powers.Text = sPowers;
        for (int i = 0; i < dvPro.Count; i++)
        {
            try
            {
                double value = double.Parse(dvPro[i]["Proportion"].ToString()) * double.Parse(dicList[dvPro[i]["Subentry"].ToString().Substring(0, dvPro[i]["Subentry"].ToString().LastIndexOf('_'))]) / 100;
                sTD = value.ToString("0.00");
                dicList.Add(dvPro[i]["Subentry"].ToString(), value.ToString("0.00"));
            }
            catch
            {
                sTD = "0.00";
            }
            int j = i + 16;
            TextBox txt = (TextBox)this.FindControl("TextBox" + j);
            txt.Text = sTD;
        }
        //3.分配柴油
        sPowers = String.Empty;
        sPowers = SQLHelper.ExecuteString("select sum([DieselFuel]) from rt_EnergyConsumption where years>='" + sStartYears + "' and years<='" + sEndYears + "' and Area_ID='41'");
        sPowers = String.IsNullOrEmpty(sPowers) ? "0.00" : sPowers;
        dvPro = SQLHelper.ExecuteDataView("select Subentry,Proportion from tb_EnergyProportion where Area_ID='-1' and Years='" + sStartYears + "' and Subentry like 'DieselFuel_%'");
        dicList.Clear();
        dicList.Add("DieselFuel", sPowers);
        powers = (TextBox)this.FindControl("TextBox18");
        powers.Text = sPowers;
        for (int i = 0; i < dvPro.Count; i++)
        {
            try
            {
                double value = double.Parse(dvPro[i]["Proportion"].ToString()) * double.Parse(dicList[dvPro[i]["Subentry"].ToString().Substring(0, dvPro[i]["Subentry"].ToString().LastIndexOf('_'))]) / 100;
                sTD = value.ToString("0.00");
                dicList.Add(dvPro[i]["Subentry"].ToString(), value.ToString("0.00"));
            }
            catch
            {
                sTD = "0.00";
            }
            int j = i + 19;
            TextBox txt = (TextBox)this.FindControl("TextBox" + j);
            txt.Text = sTD;
        }
    }
    protected void btnimport_Click(object sender, EventArgs e)
    {
        DataView mydv = new DataView();
        string mysql = " SELECT RTRIM(b.name) AS colname, CASE WHEN h.id IS NOT NULL " +
                           "       THEN 'PK' ELSE '' END AS primarykey, type_name(b.xusertype) " +
                           "       + CASE WHEN b.colstat & 1 = 1 THEN '[ID(' + CONVERT(varchar, ident_seed(a.name)) " +
                           "       + ',' + CONVERT(varchar, ident_incr(a.name)) + ')]' ELSE '' END AS type, b.length, " +
                           "       CASE b.isnullable WHEN 0 THEN 'N' ELSE 'Y' END AS isnull, ISNULL(e.text, '') " +
                           "       AS [default], ISNULL(c.[value], '') AS descript " +
                           " FROM (SELECT g.id, g.colid " +
                           "         FROM sysindexes f, sysindexkeys g " +
                           "         WHERE f.id = g.id AND f.indid = g.indid AND f.indid > 0 AND f.indid < 255 AND " +
                           "               (f.status & 2048) <> 0) h RIGHT OUTER JOIN " +
                           "       syscolumns b INNER JOIN " +
                           "       sysobjects a ON b.id = a.id LEFT OUTER JOIN " +
                           "       sys.extended_properties c ON b.id = c.major_id AND b.colid = c.minor_id LEFT OUTER JOIN " +
                           "       syscomments e ON b.cdefault = e.id ON h.id = b.id AND h.colid = b.colid " +
                           " WHERE (a.id = OBJECT_ID('tb_ReportAnalysis')) AND b.name !='P_ID'" +
                           " ORDER BY b.colid";
        DataView dvField = SQLHelper.ExecuteDataView(mysql);
        string sField = String.Empty;
        for (int i = 0; i < dvField.Count; i++)
        {
            if (dvField[i]["colname"].ToString() != "RA_ID" &&
                    dvField[i]["colname"].ToString() != "Area_ID" &&
                    dvField[i]["colname"].ToString() != "Years" &&
                    dvField[i]["colname"].ToString() != "AddDate" &&
                    dvField[i]["colname"].ToString() != "OperatUser" &&
                    dvField[i]["colname"].ToString() != "OperatDate" &&
                    dvField[i]["colname"].ToString() != "Remark"
                    )
            {
                sField += "," + dvField[i]["colname"];
            }
        }
        string insertInfo = String.Empty;
        for (int i = 1; i <= 76; i++)
        {
            TextBox txt = (TextBox)this.FindControl("TextBox" + i);
            insertInfo += "," + (String.IsNullOrEmpty(txt.Text) ? "0" : txt.Text);
        }
        string sql = "select * from tb_ReportAnalysis where Years='" + sltStartYear.Value.Trim() + sltStartMonth.Value + "' and Area_ID='41'";
        DataView mydvtmp = SQLHelper.ExecuteDataView(sql);
        if (mydvtmp.Count > 0)
        {
            ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('" + sltStartYear.Value.Trim() + sltStartMonth.Value + "月数据已经入库!');</script>");
            return;
        }
        sql = "insert into tb_ReportAnalysis(Area_ID,Years,AddDate,OperatUser,OperatDate,Remark" + sField + ") values('41','" + sltStartYear.Value.Trim() + sltStartMonth.Value + "',getdate(),'1',getdate(),''" + insertInfo + ")";
        int iBack = SQLHelper.ExecuteNonQuery(sql);
        if (iBack == 1)
        {
            ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('导入成功!');</script>");
            return;
        }
        else
        {
            ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('导入失败!');</script>");
            return;
        }
    }
}
